﻿namespace Access.BenhNhan
{
    using ConfigDatabase.Class;
    using Npgsql;
    using Access;
    using BaseClass.BenhNhan;
    using BaseClass.VienPhi;
    using System;
    using System.Data;

    public class BNKhamBenh
    {
        private ConnectDatabase _condata;
        private NpgsqlConnection _conn;
        private General _xuli;

        public BNKhamBenh()
        {
            this._condata = new ConnectDatabase();
            this._xuli = new General(this._condata);
        }

        public BNKhamBenh(ConnectDatabase condata)
        {
            this._condata = condata;
            this._xuli = new General(this._condata);
        }

        public long f_insert(dtoBNBenhAnKB dtokhambenh)
        {
            long pIDQL;
            this._conn = new NpgsqlConnection(this._condata.StringConnect);
            this._conn.Open();
            try
            {
                if (!dtokhambenh.Insert(this._conn))
                {
                    return 0L;
                }
                pIDQL = dtokhambenh.pIDQL;
            }
            catch
            {
                pIDQL = 0L;
            }
            finally
            {
                this._conn.Close();
                this._conn = null;
            }
            return pIDQL;
        }

        public DataTable f_loaddata_bnkhambenh(long idql)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = string.Concat(new object[] { 
                "select a.", dtoBNBenhAnKB.columnIDQL, " as idql,a.", dtoBNBenhAnKB.columnIDDOITUONG, " as iddoituong,to_char(", dtoBNBenhAnKB.columnNGAYVAO, ",'dd/mm/yyyy hh24:mi') as ngay,a.", dtoBNBenhAnKB.columnIDPHONG, " as idphong,a.", dtoBNBenhAnKB.columnMABN, " as mabn,b.", dtoBNThongTinBN.columnHOTEN, " as hoten,a.", dtoBNBenhAnKB.columnBENHCHINH, " as chandoan,a.", dtoBNBenhAnKB.columnIDDANGNHAP, 
                ",a2.", dtoBNDMDoiTuong.columnTEN, " as tendoituong,a3.", dtoBNDMPhong.columnTEN, " as tenphong from ", str, dtoBNBenhAnKB.tableName, " a inner join ", str, dtoBNThongTinBN.tableName, " b on a.", dtoBNBenhAnKB.columnMABN, "=b.", dtoBNThongTinBN.columnMABN, " left join ", str, 
                dtoBNDMDoiTuong.tableName, " a2 on a.", dtoBNBenhAnKB.columnIDDOITUONG, "=a2.", dtoBNDMDoiTuong.columnID, " left join ", str, dtoBNDMPhong.tableName, " a3 on a.", dtoBNBenhAnKB.columnIDPHONG, "=a3.", dtoBNDMPhong.columnID, " where a.", dtoBNBenhAnKB.columnIDQL, "=", idql, 
                " order by a.", dtoBNBenhAnKB.columnNGAYVAO, ",", dtoBNBenhAnKB.columnIDQL, " desc"
             });
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_bnkhambenh(string mabn)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = "select a." + dtoBNBenhAnKB.columnIDQL + " as idql,a." + dtoBNBenhAnKB.columnIDDOITUONG + " as iddoituong,to_char(" + dtoBNBenhAnKB.columnNGAYVAO + ",'dd/mm/yyyy hh24:mi') as ngay,a." + dtoBNBenhAnKB.columnIDPHONG + " as idphong,a." + dtoBNBenhAnKB.columnMABN + " as mabn,b." + dtoBNThongTinBN.columnHOTEN + " as hoten,a2." + dtoBNDMDoiTuong.columnTEN + " as tendoituong,a3." + dtoBNDMPhong.columnTEN + " as tenphong,a." + dtoBNBenhAnKB.columnBENHCHINH + " as chandoan,a." + dtoBNBenhAnKB.columnBENHKEMTHEO + " as chandoankt,a." + dtoBNBenhAnKB.columnGHICHU + " as ghichu,a." + dtoBNBenhAnKB.columnIDBACSI + " as idbacsi,a." + dtoBNBenhAnKB.columnIDBENHCHINH + " as idchandoan,a." + dtoBNBenhAnKB.columnIDBENHKEMTHEO + " as idchandoankt,a." + dtoBNBenhAnKB.columnTRIEUCHUNG + " as trieuchung,a." + dtoBNBenhAnKB.columnXUTRI + " as xutri," + dtoBNBenhAnKB.columnIDDANGNHAP + " from " + str + dtoBNBenhAnKB.tableName + " a inner join " + str + dtoBNThongTinBN.tableName + " b on a." + dtoBNBenhAnKB.columnMABN + "=b." + dtoBNThongTinBN.columnMABN + " left join " + str + dtoBNDMDoiTuong.tableName + " a2 on a." + dtoBNBenhAnKB.columnIDDOITUONG + "=a2." + dtoBNDMDoiTuong.columnID + " left join " + str + dtoBNDMPhong.tableName + " a3 on a." + dtoBNBenhAnKB.columnIDPHONG + "=a3." + dtoBNDMPhong.columnID + " where a." + dtoBNBenhAnKB.columnMABN + "='" + mabn + "' order by a." + dtoBNBenhAnKB.columnNGAYVAO + " desc," + dtoBNBenhAnKB.columnIDQL + " desc";
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_chidinh(string mabn)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = "select a." + dtoBNChiDinh.columnIDQL + " as idql,a." + dtoBNChiDinh.columnID + " as id,a." + dtoBNChiDinh.columnMABN + " as mabn,to_char(" + dtoBNChiDinh.columnNGAY + ",'dd/mm/yyyy hh24:mi') as ngay,a." + dtoBNChiDinh.columnIDVP + " as idvp,a." + dtoBNChiDinh.columnSOLUONG + " as soluong,a." + dtoBNChiDinh.columnDONGIA + " as dongia,a." + dtoBNChiDinh.columnSLDATHANHTOAN + " as sldathanhtoan,a." + dtoBNChiDinh.columnSOLUONG + "*a." + dtovpDMVienPhi.columnDONGIA + " as thanhtien,b." + dtovpDMVienPhi.columnMA + " as mavp,b." + dtovpDMVienPhi.columnTEN + " as tenvp,b." + dtovpDMVienPhi.columnDVT + " as dvt from " + str + dtoBNChiDinh.tableName + " a inner join " + str + dtovpDMVienPhi.tableName + " b on a." + dtoBNChiDinh.columnIDVP + "=b." + dtovpDMVienPhi.columnID + " where a.mabn='" + mabn + "' order by a." + dtoBNChiDinh.columnNGAY + " desc";
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public long f_update(dtoBNBenhAnKB dtokhambenh)
        {
            long pIDQL;
            this._conn = new NpgsqlConnection(this._condata.StringConnect);
            this._conn.Open();
            try
            {
                if (!dtokhambenh.Update(this._conn) && !dtokhambenh.Insert(this._conn))
                {
                    return 0L;
                }
                pIDQL = dtokhambenh.pIDQL;
            }
            catch
            {
                pIDQL = 0L;
            }
            finally
            {
                this._conn.Close();
                this._conn = null;
            }
            return pIDQL;
        }
    }
}

